package com.huaxia.dao.promotion;

import com.huaxia.pojo.promotion.SaleDeptSBM;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/09 14:31
 */


public interface SaleDeptSbmMapper {

    @Select("select b.saledeptsname,b.saleDeptNumSBM,a.saleDeptQuarterNumSBM,round(decode(b.saledeptNumSBM,0,0,a.saledeptQuarterNumSBM*100/b.saledeptNumSBM),1)saleDeptYieldSBM from\n" +
            "            (select sb.saledeptsname,NVL(a.num,0)saledeptQuarterNumSBM from\n" +
            "            (select t.saledeptcode,count(t.saledeptcode)num from D_AGENT_PROVINCE_YX2 t where t.agentgrade='SBM' and to_char(t.startdate,'q')=to_char(sysdate,'q') and t.agentstate='在职' and t.deptcode1 is not null  group by t.saledeptcode)a right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            "        )a,\n" +
            "                (select sb.saledeptsname,NVL(a.num,0)saledeptNumSBM from\n" +
            "            (select t.saledeptcode,count(t.saledeptcode)num from D_AGENT_PROVINCE_YX2 t where t.agentgrade='SBM' and t.agentstate='在职' and t.deptcode1 is not null group by t.saledeptcode)a right join SD_SALEDEPT sb on sb.saledeptcode=a.saledeptcode\n" +
            "        )b where a.saledeptsname=b.saledeptsname\n" +
            " ORDER BY a.saleDeptQuarterNumSBM desc")
    List<SaleDeptSBM> getSaleDeptSBM();
}
